package com.erp.dao.impl;

import com.erp.bean.Emp;
import com.erp.dao.EmpDao;
import com.erp.util.db.DataBaseDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lyb
 * 创建员工数据访问实现类
 */
public class EmpDaoImpl extends DataBaseDao implements EmpDao {
    @Override
    public List<Emp> finAll() {
        List<Emp> list=new ArrayList<>();
        Emp emp=null;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql="select eid,ename,tel,email,pass,sex,age,did,rid from emp ";
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                emp=new Emp(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getInt(7),
                        rs.getInt(8),rs.getInt(9));
                list.add(emp);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public Emp findById(int eid) {
        Emp emp=null;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql="select eid,ename,tel,email,pass,sex,age,did,rid from emp where eid=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,eid);
            rs=pstmt.executeQuery();
            while (rs.next()){emp=new Emp(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getInt(7),
                    rs.getInt(8),rs.getInt(9));}


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return emp;
    }

    @Override
    public Emp findByNameAndPass(String name, String pass) {
        Emp emp=null;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql="select eid,ename,tel,email,pass,sex,age,did,rid from emp where ename=?and pass=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,name);
            pstmt.setString(2,pass);
            rs=pstmt.executeQuery();
            while (rs.next()){
                emp=new Emp(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getInt(7),
                    rs.getInt(8),rs.getInt(9));}


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return emp;
    }

    @Override
    public int update(Emp emp) {
        int i=0;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql="update emp set eid=?,ename=?,pass=?,tel=?,email=?,sex=?,age=?,rid=?,did=?  where eid=? ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,emp.getEid());
            pstmt.setString(2,emp.getEname());
            pstmt.setString(3,emp.getPass());
            pstmt.setString(4,emp.getTel());
            pstmt.setString(5,emp.getEmail());
            pstmt.setString(6,emp.getSex());
            pstmt.setInt(7,emp.getAge());
            pstmt.setInt(8,emp.getRid());
            pstmt.setInt(9,emp.getDid());
            pstmt.setInt(10,emp.getEid());
            i=pstmt.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
          close();
        }
        return i;
    }

    @Override
    public Emp findByName(String ename) {
        Emp emp=null;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql="select eid,ename,tel,email,pass,sex,age,did,rid from emp where ename=? ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,ename);
            rs=pstmt.executeQuery();
            while (rs.next()){
                emp=new Emp(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getInt(7),
                        rs.getInt(8),rs.getInt(9));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return emp;
    }

    @Override
    public int addEmp(Emp emp) {
        int i=0;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql="insert into emp (ename,pass,tel,email,sex,age,did,rid) value (?,?,?,?,?,?,?,?) ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,emp.getEname());
            pstmt.setString(2,emp.getPass());
            pstmt.setString(3,emp.getTel());
            pstmt.setString(4,emp.getEmail());
            pstmt.setString(5,emp.getSex());
            pstmt.setInt(6,emp.getAge());
            pstmt.setInt(7,emp.getDid());
            pstmt.setInt(8,emp.getRid());
            i=pstmt.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return i;
    }

    @Override
    public List<Emp> findBydid(int did) {
        List<Emp> list=new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            String sql="select eid,ename,tel,email,pass,sex,age,did,rid from emp where did=? ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,did);
            rs=pstmt.executeQuery();
            while (rs.next()){
                Emp emp=new Emp(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getInt(7),
                        rs.getInt(8),rs.getInt(9));
                list.add(emp);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }

        return list;
    }

    @Override
    public List<Emp> find(String column, String keywords) {
        List<Emp> list=new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            String sql="select eid,ename,tel,email,pass,sex,age,did,rid from emp where "+column+" like ?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,"%"+keywords+"%");
            rs=pstmt.executeQuery();
            while (rs.next()){
                Emp emp=new Emp(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getInt(7),
                        rs.getInt(8),rs.getInt(9));
                list.add(emp);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return list;
    }
}
